﻿using System;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.ServiceProcess;
using System.Data.Sql;
using System.IO;
using System.Data.SqlServerCe;
using System.Security.Cryptography;
using System.Diagnostics;


namespace Charities.Forms
{
    public partial class ConnetionBuilder : Form
    {
        public ConnetionBuilder()
        {
            InitializeComponent();
        }
        string serverName = string.Empty;
        string InitialCataloge = string.Empty;
        string dbName = String.Empty;
        
        private void ConnetionBuilder_Load(object sender, EventArgs e)
        {
            //GetSQLDetails(this.listboxSQLServerInstances);
        }
        private void cboServerType_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                ServiceController[] services = ServiceController.GetServices(Environment.MachineName);
                var service = services.FirstOrDefault(s => s.ServiceName == "SQLBrowser");
                if (service != null && service.Status == ServiceControllerStatus.Stopped)
                {
                    tsStatusStripLabe.Text = "محاولة تفعيل خدمة (SQL Browser) يرجى الانتظار";
                    this.Cursor = Cursors.WaitCursor;
                    System.Diagnostics.Process process = new System.Diagnostics.Process();
                    System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
                    startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
                    startInfo.FileName = "cmd.exe";
                    startInfo.Arguments = "/C sc config sqlbrowser start= auto";
                    process.StartInfo = startInfo;
                    process.Start();
                    startInfo.Arguments = "/C net start sqlbrowser";
                    process.StartInfo = startInfo;
                    process.Start();
                    GetServersInstances();
                    //service.Start();
                    //service.WaitForStatus(ServiceControllerStatus.StartPending);
                    this.Cursor = Cursors.Default;
                    tsStatusStripLabe.Text = "تم تفعيل الخدمة ";
                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show("يرجى تفعيل SQL Server Browser يتطلب تشغيل الخدمة تشغيل البرنامج كمستخدم رئيسي (Run As Administrator)","لم يتمكن البرنامج من تفعيل خدمة SQL Browser"+"\n"+ex.Message,MessageBoxButtons.OK,MessageBoxIcon.Error);
                tsStatusStripLabe.Text = " لم  يتم تفعيل الخدمة جاري محاولة الاستكشاف بدون الخدمة ";
            } 
            if (cboServerType.SelectedIndex == 0)
            {
                grbAuthent.Visible = true;
                grbInstanses.Visible = true;
                grbSQLCE.Visible = false;
                cboServerName.Enabled = true;
                label2.Enabled = true;
                btnRefresh.Enabled = true;
                this.Width = 394;
                this.Height = 566;
                GetServersInstances();

            }
            else
            {
                grbSQLCE.Visible = true;
                grbSQLCE.Location = new Point(12, 124);
                grbAuthent.Visible = false;
                grbInstanses.Visible = false;
                cboServerName.Enabled = false;
                label2.Enabled = false;
                btnRefresh.Enabled = false;
                this.Width = 394;
                this.Height = 320;
            }
        }
        private void GetServersInstances()
        {
            Cursor = Cursors.WaitCursor;
            tsStatusStripLabe.Text = "يرجى الانتظار قد تتطلب العملية أكثر من دقيقة ";
            cboServerName.Items.Clear();
            var inst = SqlDataSourceEnumerator.Instance;
            DataTable table = inst.GetDataSources();
            foreach (System.Data.DataRow row in table.Rows)
            {
                string name = string.Format("{0}\\{1}", row[0], row[1]);
                cboServerName.Items.Add(name);
            }
            Cursor = Cursors.Default;
            tsStatusStripLabe.Text = "جاهز... ";
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            if (cboServerType.SelectedIndex == 0)
            {
                GetServersInstances();
            }
        }

        private void rbServerAuthentication_CheckedChanged(object sender, EventArgs e)
        {
            lstboxDBs.Items.Clear();
            btnTestConnection.Enabled = false;
            if (rbServerAuthentication.Checked)
            {
                txbPassword.Enabled = true;
                txbUserName.Enabled = true;
                label3.Enabled = true;
                label4.Enabled = true;
            }
            else
            {
                txbUserName.Enabled = false;
                txbPassword.Enabled = false;
                label3.Enabled = false;
                label4.Enabled = false;
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void txbPassword_Validated(object sender, EventArgs e)
        {
            lstboxDBs.Items.Clear();
            GetLocalInstatnces(txbUserName.Text,txbPassword.Text);
        }

        private void btnTestConnection_Click(object sender, EventArgs e)
        {

            string constring = CreateDataSourceConnectionString();
            if (cboServerType.SelectedIndex == 0)
            {
                SqlConnection conn = new SqlConnection(constring);
                try
                {
                    conn.Open();
                    MessageBox.Show("تم الاتصال بقاعدة البيانات", "تأكيد الاتصال", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    btnSaveAndClose.Enabled = true;
                    
                }
                catch (Exception)
                {

                    MessageBox.Show("خطأ في  الاتصال بقاعدة البيانات", "خطأ في الاتصال", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else
            {
                SqlCeConnection conn = new SqlCeConnection(constring);
                try
                {
                    conn.Open();
                    MessageBox.Show("تم الاتصال بقاعدة البيانات", "تأكيد الاتصال", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    btnSaveCEConnection.Enabled = true;
                }
                catch (Exception)
                {

                    MessageBox.Show("خطأ في  الاتصال بقاعدة البيانات", "خطأ في الاتصال", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

        }
        private string CreateDataSourceConnectionString()
        {
            if (cboServerType.SelectedIndex == 0)
            {
                if (rbServerAuthentication.Checked)
                {
                    return "Data Source=" + serverName + ";Initial Catalog=" + dbName + ";User ID=" + txbUserName.Text + ";Password=" + txbPassword.Text;
                }
                else
                {
                    return "Data Source=" + serverName + ";Initial Catalog=" + dbName + ";Integrated Security=SSPI";
                }
            }
            else
            {
                if (txbDBCEPWD.Text != string.Empty)
                {
                    return "Data Source=" + dbName + ";Password=" + txbDBCEPWD.Text;
                }
                else
                {
                    return "Data Source=" + dbName;
                }
            }
        }
        private void cboServerName_SelectedIndexChanged(object sender, EventArgs e)
        {
            serverName = cboServerName.Text;
            grbAuthent.Enabled = true;
        }

        private void lstboxDBs_Click(object sender, EventArgs e)
        {
            if (lstboxDBs.SelectedItem != null)
            {
                btnTestConnection.Enabled = true;
                dbName = lstboxDBs.SelectedItem.ToString();
            }
        }
        private void GetLocalInstatnces()
        {
            lstboxDBs.Items.Clear();
            int counter = 0;
            SqlCommand com = new SqlCommand("SELECT * FROM sys.databases");
            SqlConnection conn = new SqlConnection("Data Source=" + serverName + ";Initial Catalog=master;Integrated Security=SSPI");
            com.Connection = conn;
            try
            {
                conn.Open();
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    
                    if (read.GetString(0) == "Charities")
                    {
                        counter++;
                    }
                    lstboxDBs.Items.Add(read.GetString(0));
                }

                if (counter == 0)
                {
                    CreateDB();
//                    GetLocalInstatnces();
                    lstboxDBs.Items.Add(read.GetString(0));
                }
                else
                {
                    MessageBox.Show("تم العثور على قاعدة البيانات يرجى الاتصال بقاعدة البيانات Charities والضغط على فحص الاتصال ومن ثم حفظ وإغلاق", "تم العثور على قاعدة البيانات", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                conn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(" إضغط على زر انشاء, سيتم التأكد أن المخدم مفعل و خيارات الاتصال الصحيحة", "محاولة الاتصال", MessageBoxButtons.OK, MessageBoxIcon.Error);                
            }
          
        }
        private void CreateDB()
        {
            SqlConnection conn = new SqlConnection("Data Source=" + cboServerName.Text + ";Initial Catalog=master;Integrated Security=SSPI");
            conn.Open();
            SqlCommand cmd = new SqlCommand(InitDB(), conn);
            cmd.ExecuteNonQuery();
            cmd = new SqlCommand(GetScript(), conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        private void CreateDB(string uid,string pwd)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + cboServerName.Text + ";Initial Catalog=master;User ID=" + uid + ";Password=" + pwd);
            conn.Open();
            SqlCommand cmd = new SqlCommand(InitDB(), conn);
            cmd.ExecuteNonQuery();
            cmd = new SqlCommand(GetScript(), conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }


        private string GetScript()
        {
            FileInfo file = new FileInfo("Script.sql");
            string script = file.OpenText().ReadToEnd();
            script=script.Replace("\r\nGO\r\n", "\n");
            return script;
        }
        private string InitDB()
        {
            FileInfo file = new FileInfo("NEWDB.sql");
            string script = file.OpenText().ReadToEnd();
            script = script.Replace("\r\nGO\r\n", "\n");
            return script;
        }
        private void GetLocalInstatnces(string user, string pwd)
        {
            SqlCommand com = new SqlCommand("SELECT * FROM sys.databases");
            SqlConnection conn = new SqlConnection("Data Source=" + serverName + ";Initial Catalog=master;User ID=" + user + ";Password=" + pwd);
            com.Connection = conn;
            try
            {
                int cntr = 0;
                lstboxDBs.Items.Clear();
                conn.Open();
                
                SqlDataReader read = com.ExecuteReader();

                while (read.Read())
                {
                    lstboxDBs.Items.Add(read.GetString(0));
                    if (read.GetString(0) == "Charities")
                    {
                        cntr++;
                    }
                }
                if (cntr == 0)
                {
                    CreateDB(user, pwd);
                    GetLocalInstatnces(user, pwd);
                }
                else
                {
                    MessageBox.Show("تم العثور على قاعدة البيانات يرجى الاتصال بقاعدة البيانات Charities والضغط على فحص الاتصال ومن ثم حفظ وإغلاق","تم العثور على قاعدة البيانات",MessageBoxButtons.OK,MessageBoxIcon.Information);
                }
                conn.Close();

            }
            catch (Exception)
            {
                MessageBox.Show("سيتم التأكد من تفعيل المخدم واسم المستخدم و كلمة المرور, إضغط على زر انشاء ","محاولة الاتصاال",MessageBoxButtons.OK,MessageBoxIcon.Error);   
            }
        }


        private void rbWindowsAuthentication_CheckedChanged(object sender, EventArgs e)
        {
            lstboxDBs.Items.Clear();
            btnTestConnection.Enabled = false;
            if (rbWindowsAuthentication.Checked)
            {
                
                GetLocalInstatnces();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "local Database|*.sdf";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string stdfname = ofd.FileName;
                txbDBCeName.Text = Path.GetFileNameWithoutExtension(stdfname);
                dbName = ofd.FileName;
            }
        }

        private void btnSaveAndClose_Click(object sender, EventArgs e)
        {
            string datasource = CreateDataSourceConnectionString();
            string providerName=string.Empty;
            string path = "ConStr.esd";
            if (cboServerType.SelectedIndex == 0)
            {
                if (!File.Exists(path))
                {
                        using (var writer = new StreamWriter(path,true))
                        {
                            writer.WriteLine(datasource);
                            writer.Close();
                        }
                }
                else
                {
                    using (var stream = new FileStream(path, FileMode.Truncate))
                    {
                        using (var writer = new StreamWriter(stream))
                        {
                            writer.Write("data");
                        }
                        TextWriter tw = new StreamWriter(path);
                        tw.WriteLine(datasource);
                        tw.Close();
                    }
                }
            }
            else
            {
                if (!File.Exists(path))
                {
                    using (var writer = new StreamWriter(path, true))
                    {
                        writer.WriteLine(datasource);
                        writer.Close();
                    }
                }
                else
                {
                    using (var stream = new FileStream(path, FileMode.Truncate))
                    {
                        using (var writer = new StreamWriter(stream))
                        {
                            writer.Write("data");
                        }
                        TextWriter tw = new StreamWriter(path);
                        tw.WriteLine(datasource);
                        tw.Close();
                    }
                }
            }
            EncryptFile(path);
            Hide();
            var l = new Login();
            l.Show();
        }
        public void Log()
        {
            Application.Run(new Login());
        }
        private void EncryptFile(string putputFile)
        {

            try
            {
                string password = @"myKey123"; // Your Key Here
                UnicodeEncoding UE = new UnicodeEncoding();
                byte[] key = UE.GetBytes(password);
                string cryptFile = putputFile.Replace(".esd", ".tmp");
                if (File.Exists(cryptFile))
                {
                    File.Delete(cryptFile);
                    File.Copy(putputFile, cryptFile);
                }
                FileStream fsCrypt = new FileStream(cryptFile, FileMode.Create);

                RijndaelManaged RMCrypto = new RijndaelManaged();

                CryptoStream cs = new CryptoStream(fsCrypt,
                    RMCrypto.CreateEncryptor(key, key),
                    CryptoStreamMode.Write);

                FileStream fsIn = new FileStream(putputFile, FileMode.Open);

                int data;
                while ((data = fsIn.ReadByte()) != -1)
                    cs.WriteByte((byte)data);
                fsIn.Close();
                cs.Close();
                fsCrypt.Close();
                File.Delete(putputFile);
                File.Move(cryptFile, cryptFile.Replace(".tmp", ".esd"));
            }
            catch
            {
                MessageBox.Show("Encryption failed!", "Error");
            }
        }
        string FileToDecriptPath = string.Empty;
        private string DecryptFile(string putputFile)
        {
            FileToDecriptPath = putputFile.Replace(".esd", ".tmp");
            {
                string password = @"myKey123"; // Your Key Here

                UnicodeEncoding UE = new UnicodeEncoding();
                byte[] key = UE.GetBytes(password);
                string cryptFile = putputFile.Replace(".esd", ".tmp");
                if (File.Exists(cryptFile))
                {
                    File.Delete(cryptFile);
                    File.Copy(putputFile, cryptFile);
                }
                FileStream fsCrypt = new FileStream(putputFile, FileMode.Open);
                RijndaelManaged RMCrypto = new RijndaelManaged();

                CryptoStream cs = new CryptoStream(fsCrypt,
                    RMCrypto.CreateDecryptor(key, key),
                    CryptoStreamMode.Read);

                FileStream fsOut = new FileStream(cryptFile, FileMode.Create);

                int data;
                while ((data = cs.ReadByte()) != -1)
                    fsOut.WriteByte((byte)data);

                fsOut.Close();
                cs.Close();
                fsCrypt.Close();
                return FileToDecriptPath;
            }
        }

        private void ConnetionBuilder_FormClosed(object sender, FormClosedEventArgs e)
        {
            
        }

        private void txbDBCeName_TextChanged(object sender, EventArgs e)
        {
            if (txbDBCeName.Text != String.Empty)
            {
                btnTestSqlCEConnection.Enabled = true;
            }
        }

        private void btnCreatDB_Click(object sender, EventArgs e)
        {
            if (rbServerAuthentication.Checked)
            {
                GetLocalInstatnces(txbUserName.Text, txbPassword.Text);
            }
            else
            {
                GetLocalInstatnces();
            }
        }
    }
}
